package com.mx.kn.cstf2.dao.impl;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import kn.cstf2.assign.Bean.ListaTablaBean;
import kn.cstf2.global.Utils.CstF2Exception;
import kn.mx.cstf2.sql.MainSql;
import kn.mx.cstf2.sql.TransitionSql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import com.mx.kn.cstf2.dao.MainDao;
import com.mx.kn.cstf2.dto.LastUpdateImplementation;

@Repository
@Component("mainDao")
public class MainDaoImpl  implements MainDao{

	private JdbcTemplate jdbcStksf2;
	private JdbcTemplate jdbcCustomerSalesToolKit;

	@Autowired
	public void setDataSourceStkfsf2(@Qualifier("dataSourceStkfsf2") DataSource DataSourceStkfsf2) {
		this.jdbcStksf2 = new JdbcTemplate(DataSourceStkfsf2);
	}
	
	@Autowired
	public void setDataSourceCustomerSalesToolKit(@Qualifier("dataSourceCustomerSalesToolKit")
			DataSource dataSourceCustomerSalesToolKit) {
		this.jdbcCustomerSalesToolKit = new JdbcTemplate(dataSourceCustomerSalesToolKit);
		
	}

	@Override
	public void updatedFalse(Integer knkey,Integer id) {
	  
		try{
			int row=jdbcStksf2.update(MainSql.UPDATEFALSE,new Object[] {knkey,id});

		}catch(Exception e){
		
		}
	
	}


	public void updatedTrue(Integer knkey, Integer id) throws SQLException {
		int row=jdbcStksf2.update( MainSql.UPDATETRUE,new Object[] {knkey, id});	
	}


	public void updatedTrue1(Integer knkey) throws SQLException {
		int row=jdbcStksf2.update(MainSql.UPDATE1TRUE,new Object[] {knkey});
	}
	
	
	public ArrayList<ListaTablaBean> getClientes() throws SQLException {
		ArrayList<ListaTablaBean> listaTable = new ArrayList<ListaTablaBean>();
		ArrayList<Integer> listaKnkeys = new ArrayList<Integer>();

		//checar el argumento del query
		List<Map<String, Object>> rows = jdbcCustomerSalesToolKit
				.queryForList(MainSql.SELECT_KNKEY);
		for (Map row : rows) {
			ListaTablaBean listaTablaBean = new ListaTablaBean();
			listaTablaBean.setKnkey((Integer) row.get("knkey"));
			listaTablaBean.setCompanyname((String) row.get("companyname"));
			listaTablaBean.setCreationdate((String)row.get("creationdate"));
			
			List<Map<String, Object>> restDatos = jdbcStksf2
					.queryForList(MainSql.SELECCIONAR_RESTDATA1, new Object[] { listaTablaBean.getKnkey() });
			for (Map datosThisProyect : restDatos) {
				listaTablaBean.setStatus((String)datosThisProyect.get("status"));
				listaTablaBean.setStatusImple((String)datosThisProyect.get("statusimple"));
				listaTablaBean.setUpdated1((Boolean)datosThisProyect.get("updated1"));
			}
			listaTable.add(listaTablaBean);
			System.out.println("...");
		}

		return listaTable;
	}
	
	public ListaTablaBean getCliente(Integer knKey) throws SQLException {
		List<Map<String, Object>> rows = jdbcCustomerSalesToolKit
				.queryForList(MainSql.SELECT_KNKEY, new Object[] {knKey});
		ListaTablaBean listaTablaBean = new ListaTablaBean();
		for (Map row : rows) {
			
			listaTablaBean.setKnkey((Integer) row.get("knkey"));
			listaTablaBean.setCompanyname((String) row.get("companyname"));
			listaTablaBean.setCreationdate((String)row.get("creationdate"));
			
			List<Map<String, Object>> restDatos = jdbcStksf2
					.queryForList(MainSql.SELECCIONAR_RESTDATA1, new Object[] { listaTablaBean.getKnkey() });
			for (Map datosThisProyect : restDatos) {
				listaTablaBean.setStatus((String)datosThisProyect.get("status"));
				listaTablaBean.setStatusImple((String)datosThisProyect.get("statusimple"));
				listaTablaBean.setUpdated1((Boolean)datosThisProyect.get("updated1"));
			}
		}

		return listaTablaBean;
	}

	@Override
	public void insertClient(Integer idUser, Integer idTransition) {
		String sql = "INSERT INTO joinusertransition (iduser, idtransition) VALUES (?, ?)";
		int rows = jdbcStksf2
				.update(sql, new Object[] {
						idUser, idTransition});
	}

	@Override
	public boolean validaExistenciaCliente(Integer idUser, Integer idTransition) {
		String query = "SELECT COUNT(*) FROM joinusertransition WHERE iduser = ? AND idtransition = ?";
		int result = (Integer)jdbcStksf2.queryForObject(
				query, new Object[] { idUser, idTransition },
				Integer.class);
		if(result == 0)
			return false;
		else
			return true;
	}

	@Override
	public List<ListaTablaBean> getCopClient(String companyName, Integer tipoUsuario) throws SQLException {
		List<ListaTablaBean> listaCompleta = new ArrayList<ListaTablaBean>();
		String query = "SELECT knkey ,companyname, creationdate FROM t_clientes  WHERE companyname LIKE ?";
		String query2 = "SELECT trans.knkey, comp.companyname, trans.status, comp.creationdate, trans.updated "+
						"FROM t_transitions trans "+
						"INNER JOIN \"COP_COMPANIES\" comp "+
						"ON trans.\"id\" = comp.idtransition "+
						"WHERE comp.companyname LIKE ? "+
						"AND trans.\"tipoTransition\" = ?";		
		String finalName= "%"+companyName+"%";
		List<ListaTablaBean> listaTablaBean = jdbcCustomerSalesToolKit.query(
				query, new Object[] {finalName},new BeanPropertyRowMapper(ListaTablaBean.class));
				
		List<ListaTablaBean> listaTablaBean2 = jdbcStksf2.query(
				query2, new Object[] {finalName, tipoUsuario},new BeanPropertyRowMapper(ListaTablaBean.class));
		
		for(ListaTablaBean registros : listaTablaBean){
			listaCompleta.add(registros);
		}
		for(ListaTablaBean registros2 : listaTablaBean2){
			listaCompleta.add(registros2);
		}
		
		return listaCompleta;
	}
}
